package com.talent.utils;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import jakarta.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

/**
 * Excel工具类
 */
public class ExcelUtil {

    /**
     * 读取Excel文件
     */
    public static List<List<String>> readExcel(InputStream inputStream) throws IOException {
        List<List<String>> data = new ArrayList<>();
        
        try (Workbook workbook = WorkbookFactory.create(inputStream)) {
            Sheet sheet = workbook.getSheetAt(0);
            
            // 获取最大列数，确保所有行都读取相同数量的列
            int maxColumnNum = 0;
            for (Row row : sheet) {
                if (row.getLastCellNum() > maxColumnNum) {
                    maxColumnNum = row.getLastCellNum();
                }
            }
            
            for (Row row : sheet) {
                List<String> rowData = new ArrayList<>();
                // 按列索引读取，确保空单元格也被读取
                for (int cellIndex = 0; cellIndex < maxColumnNum; cellIndex++) {
                    Cell cell = row.getCell(cellIndex);
                    String cellValue = getCellValueWithMerged(sheet, row.getRowNum(), cellIndex);
                    rowData.add(cellValue);
                }
                data.add(rowData);
            }
        }
        
        return data;
    }

    /**
     * 写入Excel文件
     */
    public static void writeExcel(List<String> headers, List<List<String>> data, 
                                  String fileName, HttpServletResponse response) throws IOException {
        try (Workbook workbook = new XSSFWorkbook()) {
            Sheet sheet = workbook.createSheet("Sheet1");
            
            // 创建标题行
            Row headerRow = sheet.createRow(0);
            for (int i = 0; i < headers.size(); i++) {
                Cell cell = headerRow.createCell(i);
                cell.setCellValue(headers.get(i));
                
                // 设置标题样式
                CellStyle headerStyle = workbook.createCellStyle();
                Font font = workbook.createFont();
                font.setBold(true);
                headerStyle.setFont(font);
                cell.setCellStyle(headerStyle);
            }
            
            // 写入数据
            for (int i = 0; i < data.size(); i++) {
                Row row = sheet.createRow(i + 1);
                List<String> rowData = data.get(i);
                for (int j = 0; j < rowData.size(); j++) {
                    Cell cell = row.createCell(j);
                    cell.setCellValue(rowData.get(j));
                }
            }
            
            // 自动调整列宽
            for (int i = 0; i < headers.size(); i++) {
                sheet.autoSizeColumn(i);
            }
            
            // 设置响应头
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setHeader("Content-Disposition", "attachment; filename=" + fileName);
            
            workbook.write(response.getOutputStream());
        }
    }

    /**
     * 获取单元格值（支持合并单元格）
     */
    private static String getCellValueWithMerged(Sheet sheet, int rowIndex, int columnIndex) {
        Row row = sheet.getRow(rowIndex);
        if (row == null) {
            return "";
        }
        
        Cell cell = row.getCell(columnIndex);
        
        // 首先检查是否为合并单元格
        String mergedValue = getMergedCellValue(sheet, rowIndex, columnIndex);
        if (mergedValue != null) {
            return mergedValue;
        }
        
        // 如果不是合并单元格，则正常获取值
        return getCellValue(cell);
    }
    
    /**
     * 获取合并单元格的值
     */
    private static String getMergedCellValue(Sheet sheet, int rowIndex, int columnIndex) {
        // 遍历所有合并区域
        for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
            CellRangeAddress mergedRegion = sheet.getMergedRegion(i);
            
            // 检查当前单元格是否在合并区域内
            if (mergedRegion.isInRange(rowIndex, columnIndex)) {
                // 获取合并区域的第一个单元格（左上角）的值
                Row firstRow = sheet.getRow(mergedRegion.getFirstRow());
                if (firstRow != null) {
                    Cell firstCell = firstRow.getCell(mergedRegion.getFirstColumn());
                    return getCellValue(firstCell);
                }
            }
        }
        
        return null; // 不是合并单元格
    }

    /**
     * 获取单元格值
     */
    private static String getCellValue(Cell cell) {
        if (cell == null) {
            return "";
        }
        
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getDateCellValue().toString();
                } else {
                    return String.valueOf((long) cell.getNumericCellValue());
                }
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case FORMULA:
                return cell.getCellFormula();
            default:
                return "";
        }
    }
}
